package com.rockontrol.ele.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;

import com.rksp.util.DatabaseUtil;
import com.rksp.util.StringUtil;
import com.rockontrol.ele.pojo.Ma出入库单;
import com.rockontrol.ele.pojo.Ma出入库明细;
import com.rockontrol.ele.pojo.Ma帐套;
import com.rockontrol.ele.pojo.Ma物资库存;
import com.rockontrol.ele.pojo.Ma物资消耗台帐;
import com.rockontrol.ele.pojo.Ma盘库报告;
import com.rockontrol.ele.pojo.Ma盘库盈亏明细;
import com.rockontrol.ele.pojo.Ma结算汇总;
import com.rockontrol.ele.service.MaStorageSvc;
import com.rockontrol.ele.service.model.dbtransaction.MaStorageTrans;
import com.rockontrol.ele.service.skeleton.MaStorageSvcSkeleton;
import com.rockontrol.ele.vo.AllocationCdt;
import com.rockontrol.ele.vo.CheckInventoryCdt;
import com.rockontrol.ele.vo.InOutDepotReceiptCdt;
import com.rockontrol.ele.vo.MaConsumeCdt;
import com.rockontrol.ele.vo.MaterialClearingCdt;
import com.rockontrol.ele.vo.MaterialInventoryCdt;
import com.rockontrol.ele.vo.MaterialJournalCdt;
import com.rockontrol.ele.vo.Ma物资流水帐;

public class MaStorageSvcImpl extends MaStorageSvcSkeleton implements MaStorageSvc{
	private static Logger log = Logger.getLogger(MaStorageSvcImpl.class);
	
	private MaStorageTrans maStorageTrans;
	//帐套服务
	public String createTheInventoryAllocation (Ma帐套 ia_){
		try {
			return maStorageTrans.serviceCreateTheInventoryAllocation(ia_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public Ma帐套 getTheInventoryAllocation(String id_){
		try {
			Ma帐套 ia_=(Ma帐套) service.findObj(Ma帐套.class, id_);
			List<Ma帐套> parent=null;
			String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
			if("SQLSERVER".equals(dataBaseType)){
			        //数据库为sqlserver时执行的操作
			    ia_.setChildren(service.findSQLAlias("select convert(varchar(32),a.OID) oid,a.备注,a.部门单位,convert(varchar(32),a.单位ID ) 单位id,a.单位编码,a.管理员,a.类型,a.录入人,convert(varchar(32),a.录入人ID) 录入人id,a.录入时间,a.台帐名称,a.状态 from MA_帐套 a left join MA_帐套关系 b on a.OID=b.子节点ID where b.父节点ID='"+id_+"'",Ma帐套.class));
	            parent=service.findSQLAlias("select convert(varchar(32),a.OID) oid,a.备注,a.部门单位,convert(varchar(32),a.单位ID ) 单位id,a.单位编码,a.管理员,a.类型,a.录入人,convert(varchar(32),a.录入人ID) 录入人id,a.录入时间,a.台帐名称,a.状态 from MA_帐套 a left join MA_帐套关系 b on a.OID=b.父节点ID where b.子节点ID='"+id_+"'",Ma帐套.class);
			}else if("ORACLE".equals(dataBaseType)){
			        //数据库为oracle时执行的操作
			    ia_.setChildren(service.findSQLAlias("select to_char(a.OID) \"oid\",a.备注,a.部门单位,to_char(a.单位ID ) \"单位id\",a.单位编码,a.管理员,a.类型,a.录入人,to_char(a.录入人ID) \"录入人id\",a.录入时间,a.台帐名称,a.状态 from MA_帐套 a left join MA_帐套关系 b on a.OID=b.子节点ID where b.父节点ID='"+id_+"'",Ma帐套.class));
			    parent=service.findSQLAlias("select to_char(a.OID) \"oid\",a.备注,a.部门单位,to_char(a.单位ID ) \"单位id\",a.单位编码,a.管理员,a.类型,a.录入人,to_char(a.录入人ID) \"录入人id\",a.录入时间,a.台帐名称,a.状态 from MA_帐套 a left join MA_帐套关系 b on a.OID=b.父节点ID where b.子节点ID='"+id_+"'",Ma帐套.class);
			}
			ia_.setParent(parent.isEmpty()?null:parent.get(0));
			return ia_;
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public List<Ma帐套> getInventoryAllocations(AllocationCdt cdt_){
		try {
			return service.findObjsHQL(getInvertoryAllocationsSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public String pageInitGetInventoryAllocations (AllocationCdt cdt_, String roll_){
		try {
			return pageFind(roll_,getInvertoryAllocationsSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	private String getInvertoryAllocationsSql(AllocationCdt cdt_){
		StringBuffer buffer=new StringBuffer();
		buffer.append("from Ma帐套 a where 1=1 ");
		if(cdt_!=null){
			if(!StringUtil.isEmpty(cdt_.getDeptId())){
				buffer.append("and a.单位id ='").append(cdt_.getDeptId()).append("'");
			}
			if(!StringUtil.isEmpty(cdt_.getStartDate())){
				buffer.append("and a.录入时间 >=to_date('").append(cdt_.getStartDate()).append(" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
			}
			if(!StringUtil.isEmpty(cdt_.getEndDate())){
				buffer.append("and a.录入时间 <=to_date('").append(cdt_.getEndDate()).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
			}
			if(!StringUtil.isEmpty(cdt_.getStorageName())){
				buffer.append("and a.台帐名称 like '%").append(cdt_.getStorageName()).append("%'");
			}
			if(!StringUtil.isEmpty(cdt_.getStorageId())){
                buffer.append("and a.oid = '").append(cdt_.getStorageId()).append("'");
            }
			if(!StringUtil.isEmpty(cdt_.getStorageAdminId())){
				buffer.append("and a.管理员id = '").append(cdt_.getStorageAdminId()).append("'");
			}
			if(!StringUtil.isEmpty(cdt_.getType())){
				buffer.append("and a.类型分类名 = '").append(cdt_.getType()).append("'");
			}
		}
		buffer.append("order by a.录入时间 desc");
		return buffer.toString();		
	}
	
	public Boolean deleteTheInventoryAllocation (String id_){
		try {
			return maStorageTrans.serviceDeleteTheInventoryAllocation(id_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return false;
		}
	}
	
	public Boolean updateTheInventoryAllocation (Ma帐套 ia_){
		try {
			return maStorageTrans.serviceUpdateTheInventoryAllocation(ia_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return false;
		}
	}
	
	public Ma帐套 checkTheMaterialAccount (String id_,String type){
		try {
			List<Ma帐套> list= service.findObjsHQL("from Ma帐套 a where a.单位id='"+id_+"' and a.类型分类名='"+type+"'");
			return list.isEmpty()?null:list.get(0);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public Boolean IsDeleteTheMaterialAccount (String id_){
		try {
			List<Ma物资库存> list=service.findObjsHQL("from Ma物资库存 a where a.帐套id='"+id_+"'");
			return list==null||list.isEmpty();
		} catch (Exception e) {
			log.error(e.getMessage());
			return false;
		}
	}
	
	//出入库单信息服务
	public String createTheInOutDepotReceipt(Ma出入库单 oisr_){
		try {
			return maStorageTrans.serviceCreateTheInOutDepotReceipt(oisr_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public Ma出入库单 getTheInOutDepotReceipt(String id_){
		try {
		    Ma出入库单 crk=(Ma出入库单) service.findObj(Ma出入库单.class, id_);
		    String hqlString="from Ma出入库明细  a where a.单据id='"+id_+"'";
		    crk.setStorageDetial(service.findObjsHQL(hqlString));
			return crk;
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public List<Ma出入库单> getInOutDepotReceipts(InOutDepotReceiptCdt cdt_){
		try {
			return service.findObjsHQL(getInOutDepotReceiptsSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public String pageInitGetInOutDepotReceipts (InOutDepotReceiptCdt cdt_,String roll_){
		try {
			return pageFind(roll_,getInOutDepotReceiptsSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	private String getInOutDepotReceiptsSql(InOutDepotReceiptCdt cdt_){
	    String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
		StringBuffer sql=new StringBuffer();
		sql.append("from Ma出入库单 a where 类型 !='退回' and 1=1 ");
		if(cdt_!=null){
			if(!StringUtil.isEmpty(cdt_.getInStorageStartDate())){
			    if("SQLSERVER".equals(dataBaseType)){
			        //数据库为sqlserver时执行的操作
			        sql.append(" and a.出入库时间 >= '").append(cdt_.getInStorageStartDate()).append(" 00:00:00.000' ");
    			}else if("ORACLE".equals(dataBaseType)){
    			        //数据库为oracle时执行的操作
    			    sql.append(" and a.出入库时间 >= to_date('").append(cdt_.getInStorageStartDate()).append(" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
    			}
			}
			if(!StringUtil.isEmpty(cdt_.getInStorageEndDate())){
			    if("SQLSERVER".equals(dataBaseType)){
			        //数据库为sqlserver时执行的操作
			        sql.append(" and a.出入库时间 <= '").append(cdt_.getInStorageEndDate()).append(" 23:59:59.000' ");
    			}else if("ORACLE".equals(dataBaseType)){
    			        //数据库为oracle时执行的操作
    			    sql.append(" and a.出入库时间 <= to_date('").append(cdt_.getInStorageEndDate()).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
    			}
			}
			if(!StringUtil.isEmpty(cdt_.getInStorageType())){
				sql.append(" and a.类型 = '").append(cdt_.getInStorageType()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.getMaType())){
				sql.append(" and a.类型分类 ='").append(cdt_.getMaType()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.getReceiptNumber())){
				sql.append(" and a.单据编号 like'%").append(cdt_.getReceiptNumber()).append("%' ");
			}
			if(!StringUtil.isEmpty(cdt_.getDeptName())){
				sql.append(" and (a.出单位 ='").append(cdt_.getDeptName()).append("' or a.入单位 ='").append(cdt_.getDeptName()).append("') ");
			}
			if(!StringUtil.isEmpty(cdt_.getInDepartmentID())){
			    sql.append(" and a.录入单位id='").append(cdt_.getInDepartmentID()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.getContractNumber())){
			    sql.append(" and a.合同号='").append(cdt_.getContractNumber()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.get是否登记())){
				sql.append(" and a.是否登记 ='").append(cdt_.get是否登记()).append("' ");
			}
		}
		sql.append("order by a.录入时间 desc");
		return sql.toString();		
	}
	
	public Boolean deleteTheInOutDepotReceipt (String id_){
		try {
			return maStorageTrans.serviceDeleteTheInOutDepotReceipt(id_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return false;
		}
	}
	
	public Boolean updateTheInOutDepotReceipt (Ma出入库单 oisr_){
		try {
			return maStorageTrans.serviceUpdateTheInOutDepotReceipt(oisr_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return false;
		}
	}
	
	public Ma出入库明细 getTheInOutDepotReceiptDetail (String id_){
		try {
			return (Ma出入库明细) service.findObj(Ma出入库明细.class, id_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public Ma出入库单 getLastInOutInfo(String unit){
		try {
			StringBuffer hql=new StringBuffer("from Ma出入库单 a ");
			hql.append(" where a.入单位='").append(unit).append("' order by a.出入库时间 desc");
			List<Ma出入库单> list=service.findObjsHQL(hql.toString());
			return list==null||list.isEmpty()?null:list.get(1);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public Boolean modifOutStoreroomingStatus(Ma出入库单 oisr_){
		return updateTheInOutDepotReceipt(oisr_);
	}
	public Boolean assignMauipment(Ma出入库单 oisr_){
		try {
			return maStorageTrans.serviceAssignMauipment(oisr_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}
	//流水帐服务
	public List<Ma物资流水帐> getMaterialJournalAccount (MaterialJournalCdt cdt_){
		try {
			return service.findSQLAlias(getMaterialJournalAccountSql(cdt_), Ma物资流水帐.class);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	public String pageInitGetMaterialJournalAccount (MaterialJournalCdt cdt_,String roll){
		try {
			return pageFind(roll, getMaterialJournalAccountSql(cdt_),new HashMap(),4,Ma物资流水帐.class);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public String getMaterialJournalAccountSql (MaterialJournalCdt cdt_){
		StringBuffer sql=new StringBuffer();
		String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
		if("SQLSERVER".equals(dataBaseType)){
		        //数据库为sqlserver时执行的操作
		    sql.append("select CONVERT(nvarchar(32),a.OID) as oid, b.类型 as 单据类型,b.入单位 ,b.单据编号,a.物资分类 as 物资名称,a.规格型号,a.实际单价 as 单价,a.实际数量 as 数量,a.单位,a.实际金额 as 总金额,b.出入库时间 as 操作时间,a.录入人 as 录入人,b.备注 from MA_出入库明细 as a left join MA_出入库单 as b on a.单据ID=b.OID where 1=1 ");
		}else if("ORACLE".equals(dataBaseType)){
		        //数据库为oracle时执行的操作
		    sql.append("select to_char(a.OID) as \"oid\", b.类型 as 单据类型,b.入单位 ,b.单据编号,a.物资分类 as 物资名称,a.规格型号,a.实际单价 as 单价,a.实际数量 as 数量,a.单位,a.实际金额 as 总金额,b.出入库时间 as 操作时间,a.录入人 as 录入人,b.备注 from MA_出入库明细  a left join MA_出入库单 b on a.单据ID=b.OID where 1=1 ");
		}
		if(cdt_!=null){
			if (!StringUtil.isEmpty(cdt_.get类型分类())) {

				sql.append(" and b.类型分类='").append(cdt_.get类型分类()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.get入单位())){
				sql.append(" and b.入单位 like '%").append(cdt_.get入单位()).append("%' ");
			}
			if(!StringUtil.isEmpty(cdt_.get出单位())){
				sql.append(" and b.出单位 like '%").append(cdt_.get出单位()).append("%' ");
			}
			if(!StringUtil.isEmpty(cdt_.get单据类型())){
				sql.append(" and b.类型 = '").append(cdt_.get单据类型()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.get出入库时间从())){
			    if("SQLSERVER".equals(dataBaseType)){
			        //数据库为sqlserver时执行的操作
			        sql.append(" and b.出入库时间 >= '").append(cdt_.get出入库时间从()).append(" 00:00:00.000' ");
    			}else if("ORACLE".equals(dataBaseType)){
    			        //数据库为oracle时执行的操作
    			    sql.append(" and b.出入库时间 >= to_date('").append(cdt_.get出入库时间从()).append(" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
    			}
			}
			if(!StringUtil.isEmpty(cdt_.get到())){
			    if("SQLSERVER".equals(dataBaseType)){
			        //数据库为sqlserver时执行的操作
			        sql.append(" and b.出入库时间 <= '").append(cdt_.get到()).append(" 23:59:59.000' ");
    			}else if("ORACLE".equals(dataBaseType)){
    			        //数据库为oracle时执行的操作
    			    sql.append(" and b.出入库时间 <= to_date('").append(cdt_.get到()).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
    			}
			}
			if(!StringUtil.isEmpty(cdt_.get物资名称())){
				sql.append(" and a.物资分类 = '").append(cdt_.get物资名称()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.get规格型号())){
				sql.append(" and a.规格型号 = '").append(cdt_.get规格型号()).append("' ");
			}
		}
		sql.append("order by a.录入时间 desc");
		return sql.toString();	
	}
	
	// 结算服务
	public List< Ma结算汇总> getMaterialClearingAccount (MaterialClearingCdt cdt_){
		try {
			return service.findObjsHQL(getMaterialClearingAccountSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	public String pageInitGetMaterialClearingAccount(MaterialClearingCdt cdt_,String roll_){
		try {
			return pageFind(roll_, getMaterialClearingAccountSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	private String getMaterialClearingAccountSql(MaterialClearingCdt cdt_){
		StringBuffer sql=new StringBuffer();
		sql.append("from Ma结算汇总 a where 1=1");
		
		if(cdt_!=null){
			if(!StringUtil.isEmpty(cdt_.get保管单位())){
				sql.append(" and  a.台帐单位 like '%").append(cdt_.get保管单位()).append("%' ");
			}
			if(!StringUtil.isEmpty(cdt_.get结算日期())){
				sql.append(" and  a.结算月 like '%").append(cdt_.get结算日期()).append("%' ");
			}
			if(!StringUtil.isEmpty(cdt_.getType())){
				sql.append(" and  a.类型分类 = '").append(cdt_.getType()).append("' ");
			}
		}
		
		return sql.toString();
	}

	//结算汇总
	public void finalSettlement(){
		
        try{
            maStorageTrans.serviceFinalSettlement();
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException();
        }
	}
	
	//盘库服务
	public Ma盘库报告 initTheMaterialCheckInventory (String unitId_,String type){
	    
		return null;
	}
	
	public String createTheMaterialCheckInventory (Ma盘库报告 mci_){
	    
		return maStorageTrans.serviceCreateTheMaterialCheckInventory(mci_);
	}
	private List<Ma盘库盈亏明细> getMaterialCheckDetails(String id, String empId,String orgId, boolean isBalance,String storeId,String type){
        List<Ma盘库盈亏明细> mcList = new ArrayList<Ma盘库盈亏明细>();
        StringBuffer sb_sql_sumMPInfos = new StringBuffer();
        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
        if("SQLSERVER".equals(dataBaseType)){
                //数据库为sqlserver时执行的操作
            sb_sql_sumMPInfos.append("SELECT CONVERT(VARCHAR(32),mas.物资分类ID) AS 物资分类id,mas.分类编码 AS 分类编码,mas.物资分类 AS 物资分类,"+
                    "mas.单位 AS 单位,CONVERT(VARCHAR(32),mas.规格型号ID) AS 规格型号id,mas.规格流水号 AS 规格流水号,mas.规格型号 AS 规格型号,mas.库存量   AS 库存数量 "+
                    "FROM MA_物资库存  mas,BA_物资分类  bas,MA_帐套 as zt,BA_Employee as emp  where mas.帐套ID=zt.OID and emp.OID='"+empId+"' and "+
                    "emp.ORG_ID='"+orgId+"' and zt.类型分类名='"+type+"' and bas.OID=mas.物资分类ID");
        }else if("ORACLE".equals(dataBaseType)){
                //数据库为oracle时执行的操作
            sb_sql_sumMPInfos.append("SELECT to_char(mas.物资分类ID) AS \"物资分类id\",mas.分类编码 AS 分类编码,mas.物资分类 AS 物资分类,"+
                    "mas.单位 AS 单位,to_char(mas.规格型号ID) AS \"规格型号id\",mas.规格流水号 AS 规格流水号,mas.规格型号 AS 规格型号,mas.库存量   AS 库存数量 "+
                    "FROM MA_物资库存  mas,BA_物资分类  bas,MA_帐套  zt,BA_Employee emp  where mas.帐套ID=zt.OID and emp.OID='"+empId+"' and "+
                    "emp.ORG_ID='"+orgId+"' and zt.类型分类名='"+type+"' and bas.OID=mas.物资分类ID");
        }
//        if(!StringUtil.isEmpty(id)){
//            sb_sql_sumMPInfos.append(" and mas.盘库记录ID='"+id+"'");
//        }
//        if(isBalance){
//            sb_sql_sumMPInfos.append(" and mas.盈亏差值<>'0'");
//        }
        mcList = service.findSQLAlias(sb_sql_sumMPInfos.toString(), Ma盘库盈亏明细.class);
        return mcList;
    }
	public Ma盘库报告 getTheMaterialCheckInventory (String id_,String empId_,String bindId_,boolean isBalance_,String type_){
	    try {
	           if((id_==null || id_.equals("")) && (bindId_==null || bindId_.equals(""))){
	            // 查出该人员对应的所有材料库存,初始化盘库报告
	               Ma盘库报告 ma = new  Ma盘库报告();
	               String sqlString="from Ma帐套 as zt where zt.管理员id='"+empId_+"'";
	               List<Ma帐套> ztList=service.findObjsHQL(sqlString);
	               ma.set台帐名称(ztList.get(0).get台帐名称());
	               ma.set帐套id(ztList.get(0).getOid());
	               for(int i=0;i<ztList.size();i++){
	               List<Ma盘库盈亏明细> mcList = getMaterialCheckDetails(id_,empId_,ztList.get(i).get单位id(), isBalance_, bindId_,type_);
	               for(Ma盘库盈亏明细 mcdetail:mcList){
	                   mcdetail.set状态("未清点");
	                   mcdetail.set盈亏差值(new BigDecimal(0));
	               }
	               ma.setDetailList(mcList);
	               }
	               return ma;
	           }else if(bindId_!=null && !bindId_.equals("")){
	               Ma盘库报告 ma = new  Ma盘库报告();
//	               ma = (Ma盘库报告) service.findObj(Ma盘库报告.class, id);
	               List<Ma盘库报告> mpList = ((List<Ma盘库报告>)service.findObjsHQL(" FROM Ma盘库报告 WHERE bindId='"+bindId_+"'"));
	               if(mpList !=null && mpList.size() > 0){
	                   ma = mpList.get(0); 
//	                   ma.setCheckDetails(getMaterialCheckDetails(ma.getOid(),empId,isBalance, ma.get仓库id()));
	                   ma.setDetailList((List<Ma盘库盈亏明细>)service.findObjsHQL(" FROM Ma盘库盈亏明细 model WHERE model.盘库记录id='"+ma.getOid()+"'"));
	                   return ma;
	               }else{
	                   return null;
	               }
	               
	               
	           }else if(id_!=null && !id_.equals("")){
	               Ma盘库报告 ma = new  Ma盘库报告();
	               ma = (Ma盘库报告) service.findObj(Ma盘库报告.class, id_);
//	               ma.setCheckDetails(getMaterialCheckDetails(ma.getOid(),empId,isBalance, ma.get仓库id()));
	               ma.setDetailList((List<Ma盘库盈亏明细>)service.findObjsHQL(" FROM Ma盘库盈亏明细 model WHERE model.盘库记录id='"+ma.getOid()+"'"));
	               return ma;
	           }else{
	               return null;
	           }
	        } catch (Exception e) {
	            log.error(e.getMessage());
	            e.printStackTrace();
	            return null;
	        }
	}
	
	
	public String pageInitGetCheckInventorys (CheckInventoryCdt cdt_, String roll_){
	    try {
            return pageFind(roll_, getSQL_CheckInventorys(cdt_),new HashMap(),4,Ma盘库报告.class);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
	}
	
	 private String getSQL_CheckInventorys(CheckInventoryCdt cdt){
	        StringBuffer sb_hql = new StringBuffer();
	        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
	        if("SQLSERVER".equals(dataBaseType)){
	                //数据库为sqlserver时执行的操作
	            sb_hql.append("select CONVERT(VARCHAR(32),model.OID) as oid,model.盘库编号 as 盘库编号, CONVERT(VARCHAR(32),model.盘库人ID) as 盘库人id, model.盘库人员 as 盘库人员,"+
	                    "CONVERT(VARCHAR(32),model.帐套ID) as 帐套id, CONVERT(VARCHAR(32),model.盘库部门ID) as 盘库部门id, model.盘库部门编码  as 盘库部门编码, model.盘库部门名称 as 盘库部门名称,  model.盈亏状况  as 盈亏状况,model.日期时间 as 日期时间,"+
	                    "model.类型分类  as 类型分类,model.类型分类名 as 类型分类名, model.盘库说明   as 盘库说明,model.是否平帐 as 是否平帐,  CONVERT(VARCHAR(32),model.平帐人员ID) as 平帐人员id,"+ 
	                     "model.平帐人员  as 平帐人员,CONVERT(VARCHAR(32),model.平帐单位ID) as 平帐单位id,model.平帐单位编码 as 平帐单位编码,  model.平帐单位名称 as 平帐单位名称,  model.平帐日期 as 平帐日期,"+
	                     "model.平帐说明 as 平帐说明,model.审批状态 as 状态, model.录入时间  as 录入时间,model.录入人 as 录入人,CONVERT(INT, model.BIND_ID) as bindId,mc.台帐名称 as 台帐名称  from MA_盘库报告  model,MA_帐套 mc where  model.帐套ID=mc.OID ");

	        }else if("ORACLE".equals(dataBaseType)){
	                //数据库为oracle时执行的操作
	            sb_hql.append("select to_char(model.OID) as \"oid\",model.盘库编号 as 盘库编号, to_char(model.盘库人ID) as \"盘库人id\", model.盘库人员 as 盘库人员,"+
	                    "to_char(model.帐套ID) as \"帐套id\", to_char(model.盘库部门ID) as \"盘库部门id\", model.盘库部门编码  as 盘库部门编码, model.盘库部门名称 as 盘库部门名称,  model.盈亏状况  as 盈亏状况,model.日期时间 as 日期时间,"+
	                    "model.类型分类  as 类型分类,model.类型分类名 as 类型分类名, model.盘库说明   as 盘库说明,model.是否平帐 as 是否平帐,  to_char(model.平帐人员ID) as \"平帐人员id\","+ 
	                     "model.平帐人员  as 平帐人员,to_char(model.平帐单位ID) as \"平帐单位id\",model.平帐单位编码 as 平帐单位编码,  model.平帐单位名称 as 平帐单位名称,  model.平帐日期 as 平帐日期,"+
	                     "model.平帐说明 as 平帐说明,model.审批状态 as 状态, model.录入时间  as 录入时间,model.录入人 as 录入人,to_number(model.BIND_ID) as \"bindId\",mc.台帐名称 as 台帐名称  from MA_盘库报告  model,MA_帐套 mc where  model.帐套ID=mc.OID ");

	        }

	        if(cdt!=null){
	            if(!StringUtil.isEmpty(cdt.getCheckInventoryDepartmentID())){
	                sb_hql.append(" and model.帐套ID='" + cdt.getCheckInventoryDepartmentID() + "' ");
	            }
	            if(!StringUtil.isEmpty(cdt.getCheckInventoryPerson())){
	                sb_hql.append(" and model.盘库人员 like '%" + cdt.getCheckInventoryPerson() + "%' ");
	            }
	            if(!StringUtil.isEmpty(cdt.getCheckInventoryNumber())){
	                sb_hql.append(" and model.盘库编号  like '%" + cdt.getCheckInventoryNumber() + "%' ");
	            }
	            if(!StringUtil.isEmpty(cdt.getCheckInventoryType())){
	                sb_hql.append(" and model.盈亏状况='" + cdt.getCheckInventoryType() + "' ");
	            }
	            if(!StringUtil.isEmpty(cdt.getState())){
	                sb_hql.append(" and model.审批状态='" + cdt.getState() + "' ");
	            }
	            if(!StringUtil.isEmpty(cdt.getCheckStartDate())){
	                if("SQLSERVER".equals(dataBaseType)){
	                    //数据库为sqlserver时执行的操作
	                    sb_hql.append(" and model.日期时间>='" + cdt.getCheckStartDate()  + " 00:00:00.000' ");
	                }else if("ORACLE".equals(dataBaseType)){
	                        //数据库为oracle时执行的操作
	                    sb_hql.append(" and model.日期时间 <= to_date('").append(cdt.getCheckStartDate()).append(" 00:00:00','yyyy-mm-dd hh24:mi:ss') ");
	                }
	                
	            }
	            if(!StringUtil.isEmpty(cdt.getCheckStartDate())){
	                if("SQLSERVER".equals(dataBaseType)){
	                    //数据库为sqlserver时执行的操作
	                    sb_hql.append(" and model.日期时间<='" + cdt.getCheckStartDate()  + " 23:59:59.000' ");
	                }else if("ORACLE".equals(dataBaseType)){
	                        //数据库为oracle时执行的操作
	                    sb_hql.append(" and model.日期时间 <= to_date('").append(cdt.getCheckStartDate()).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss') ");
	                }
	                
	            }
	            if(!StringUtil.isEmpty(cdt.getMaType())){
	                sb_hql.append(" and model.类型分类名 ='" + cdt.getMaType()  + "' ");
	            }
	        }
	        
	        sb_hql.append(" order by model.录入时间  desc ");
	        
	        return sb_hql.toString();
	    }
	public Boolean deleteTheMaterialCheckInventory (String id_){
	    try {
            return maStorageTrans.serviceDeleteTheMaterialCheckInventory(id_);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return false;
        }
	}
	
	public Boolean updateTheMaterialCheckInventory (Ma盘库报告 mci_){
	    try {
            return maStorageTrans.serviceUpdateTheMaterialCheckInventory(mci_);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return false;
        }
	}
	
	public Boolean balanceAccount (Ma盘库报告 mci_){
	    try {
            return maStorageTrans.serviceBalanceAccount(mci_);
        } catch (Exception e) {
            log.error(e.getMessage());
            e.printStackTrace();
            return null;
        }
	}
	
	//物资库存信息服务
	public String updateTheMaterialInventory (Ma物资库存 mi_){
		return null;
	}
	
	public Ma物资库存 getTheMaterialInventoryItem (String flid_ , String xhid_, String accId){
		try {
			List<Ma物资库存> list=service.findObjsHQL("from Ma物资库存 a where a.帐套id='"+accId+"' and a.物资分类id='"+flid_+"' and a.规格型号id='"+xhid_+"'");
			
			return list==null||list.isEmpty()?null:list.get(0);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public Ma物资库存 getMaterialInvetoryById (String id_ ){
		try {
			return (Ma物资库存) service.findObj(Ma物资库存.class, id_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	public List<Ma物资库存> getMaterialInventory(MaterialInventoryCdt miCdt_){
		try {
			return service.findSQLAlias(getMaterialInventorySql(miCdt_),Ma物资库存.class);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	public String pageInitGetMaterialInventory(MaterialInventoryCdt miCdt_, String roll_){
		try {
			return pageFind(roll_, getMaterialInventorySql(miCdt_),new HashMap(),4,Ma物资库存.class);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}
	
	private String getMaterialInventorySql(MaterialInventoryCdt miCdt_){
		StringBuffer sql=new StringBuffer();
		String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
		if("SQLSERVER".equals(dataBaseType)){
		        //数据库为sqlserver时执行的操作
		    sql.append("select distinct convert(varchar(32),a.OID) oid, a.分类编码+a.规格流水号 as 分类编码 ,b.台帐名称 as 所属仓库 ,a.物资分类 as 物资分类,a.规格型号 as 规格型号,a.单价 as 单价,a.库存量 as 库存量,a.单位 as 单位,a.阀值上限 as 阀值上限,a.阀值下限 as 阀值下限 ,a.台帐单位 as 台帐单位 , CONVERT(VARCHAR(32),a.台帐单位ID) as 台帐单位id,c.合同号 from Ma_物资库存 a left join MA_帐套 b on  a.帐套ID=b.OID");
		}else if("ORACLE".equals(dataBaseType)){
		        //数据库为oracle时执行的操作
//		    sql.append("select to_char(a.OID) as \"oid\", to_char(a.分类编码+a.规格流水号) as 分类编码 ,b.台帐名称 as 所属仓库 ,a.物资分类 as 物资分类,a.规格型号 as 规格型号,a.单价 as 单价,a.库存量 as 库存量,a.单位 as 单位,a.阀值上限 as 阀值上限,a.阀值下限 as 阀值下限 ,a.台帐单位 as 台帐单位 , to_char(a.台帐单位ID) as \"台帐单位id\" from Ma_物资库存 a ,MA_帐套 b where a.帐套ID=b.OID");
		    sql.append("select distinct to_char(a.OID) as \"oid\", to_char(a.分类编码+a.规格流水号) as 分类编码 ,b.台帐名称 as 所属仓库 ,a.物资分类 as 物资分类,a.规格型号 as 规格型号,a.单价 as 单价,a.库存量 as 库存量,a.单位 as 单位,a.阀值上限 as 阀值上限,a.阀值下限 as 阀值下限 ,a.台帐单位 as 台帐单位 , to_char(a.台帐单位ID) as \"台帐单位id\",c.合同号 from Ma_物资库存 a left join MA_帐套 b on  a.帐套ID=b.OID");
		    
		}
		sql.append(" left join (select d.物资分类,d.规格型号,e.合同号 from Ma_出入库明细 d,Ma_出入库单 e where d.单据ID=e.OID) c on a.物资分类 = c.物资分类 and a.规格型号 = c.规格型号 where 1=1 ");
		if(miCdt_!=null){
			if(!StringUtil.isEmpty(miCdt_.getMaterialType())){
				sql.append(" and a.物资分类 like '%").append(miCdt_.getMaterialType()).append("%'");
			}
			if(!StringUtil.isEmpty(miCdt_.getMaterialNumber())){
			    sql.append(" and a.分类编码 like '%").append(miCdt_.getMaterialNumber()).append("%'");
			}
			if(!StringUtil.isEmpty(miCdt_.getModel())){
				sql.append(" and a.规格型号 like '%").append(miCdt_.getModel()).append("%'");
			}
			if(!StringUtil.isEmpty(miCdt_.getAccount())){
				sql.append(" and b.台帐名称 like '%").append(miCdt_.getAccount()).append("%'");
			}
			if(!StringUtil.isEmpty(miCdt_.getDepartment())){
				sql.append(" and a.台帐单位 like '%").append(miCdt_.getDepartment()).append("%'");
			}
			if(!StringUtil.isEmpty(miCdt_.getType())){
			    sql.append(" and b.类型分类名 = '").append(miCdt_.getType()).append("'");
			}
			if(!StringUtil.isEmpty(miCdt_.getContractNumber())){
			    sql.append(" and c.合同号 = '").append(miCdt_.getContractNumber()).append("'");
			}
			if(miCdt_.isHasMx()!=null){
				if(miCdt_.isHasMx()){
					sql.append(" and (a.阀值上限 is not null or a.阀值下限 is not null)");
				}else {
					sql.append(" and (a.阀值上限 is null or a.阀值下限 is null)");
				}
			}
		}
		sql.append(" order by a.物资分类,a.规格型号");
		return sql.toString();
	}
	
	public Boolean updateMaterialInventory (Ma物资库存 mi_){
		try {
			service.modifyObj(mi_);
			return true;
		} catch (Exception e) {
			log.error(e.getMessage());
			return false;
		}
	}
	
	//分页信息服务
	public String pageNumMaStorage(String roll_,Integer num_){
		try {
			return pageNum(roll_, num_);
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}

	public MaStorageTrans getMaStorageTrans() {
		return maStorageTrans;
	}

	public void setMaStorageTrans(MaStorageTrans maStorageTrans) {
		this.maStorageTrans = maStorageTrans;
	}

    public Ma出入库单 getTheInOutDepotReceiptByBindId(String bindid){
        try {
            Ma出入库单 crk=(Ma出入库单) service.findObjsHQL("from Ma出入库单  a where a.bindId='"+bindid+"'").get(0);
            String hqlString="from Ma出入库明细  a where a.单据id='"+crk.getOid()+"'";
            crk.setStorageDetial(service.findObjsHQL(hqlString));
            return crk;
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public String createTheSendBackReceipt(Ma出入库单 oisr_){
        try {
            return maStorageTrans.ServiceCreateTheSendBackReceipt(oisr_);
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    public String pageInitGetSendBackReceipts(InOutDepotReceiptCdt cdt_, String roll_){
        try {
            return pageFind(roll_,getSendBackReceiptSql(cdt_));
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }
    private String getSendBackReceiptSql(InOutDepotReceiptCdt cdt_){
        StringBuffer sql=new StringBuffer();
        sql.append("from Ma出入库单 a where a.类型 ='退回' and 1=1 ");
        if(cdt_!=null){
            if(!StringUtil.isEmpty(cdt_.getInStorageStartDate())){
                sql.append(" and a.出入库时间 >= to_date('").append(cdt_.getInStorageStartDate()).append("00:00:00','yyyy-mm-dd hh24:mi:ss') ");
            }
            if(!StringUtil.isEmpty(cdt_.getInStorageEndDate())){
                sql.append(" and a.出入库时间 <= to_date('").append(cdt_.getInStorageEndDate()).append("23:59:59','yyyy-mm-dd hh24:mi:ss') ");
            }
            if(!StringUtil.isEmpty(cdt_.getInStorageType())){
                sql.append(" and a.类型 = '").append(cdt_.getInStorageType()).append("' ");
            }
            if(!StringUtil.isEmpty(cdt_.getMaType())){
                sql.append(" and a.类型分类 ='").append(cdt_.getMaType()).append("' ");
            }
            if(!StringUtil.isEmpty(cdt_.getReceiptNumber())){
                sql.append(" and a.单据编号 like'%").append(cdt_.getReceiptNumber()).append("%' ");
            }
            if(!StringUtil.isEmpty(cdt_.getDeptName())){
                sql.append(" and (a.出单位 ='").append(cdt_.getDeptName()).append("' or a.入单位 ='").append(cdt_.getDeptName()).append("') ");
            }
        }
        sql.append("order by a.录入时间 desc");
        return sql.toString();      
    }

	public String createConsumeAcc(Ma物资消耗台帐 ca_) {
		try {
			return maStorageTrans.serviceCreateConsumeAcc(ca_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}
	
	public Boolean updateConsumeAcc(Ma物资消耗台帐 ca_) {
		try {
			return maStorageTrans.serviceUpdateConsumeAcc(ca_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}


	public Boolean deleteConsumeAcc(String id_) {
		try {
			return maStorageTrans.serviceDeleteConsumeAcc(id_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public Ma物资消耗台帐 getConsumeAcc(String id_) {
		try {
			return (Ma物资消耗台帐) service.findObj(Ma物资消耗台帐.class, id_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public List<Ma物资消耗台帐> getConsumeAccs(MaConsumeCdt cdt_) {
		try {
			return service.findObjsHQL(getConsumeAccsSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage());
			return null;
		}
	}

	public String pageInitGetConsumeAccs(MaConsumeCdt cdt_, String roll) {
		try {
            return pageFind(roll,getConsumeAccsSql(cdt_));
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
	}

	private String getConsumeAccsSql(MaConsumeCdt cdt_){
		StringBuffer sql=new StringBuffer();
		sql.append("from Ma物资消耗台帐 a where 1=1 ");
		if(cdt_!=null){
			if(!StringUtil.isEmpty(cdt_.get开始时间()))
				sql.append(" and a.消耗日期 >= to_date('").append(cdt_.get开始时间()).append("00:00:00','yyyy-mm-dd hh24:mi:ss') ");
			if(!StringUtil.isEmpty(cdt_.get结束时间()))
				sql.append(" and a.消耗日期 <= to_date('").append(cdt_.get开始时间()).append("23:59:59','yyyy-mm-dd hh24:mi:ss') ");
			if(!StringUtil.isEmpty(cdt_.get消耗单位()))
				sql.append(" and a.消耗单位 = '").append(cdt_.get开始时间()).append("' ");
			if(!StringUtil.isEmpty(cdt_.get物资分类()))
				sql.append(" and a.物资分类 like '%").append(cdt_.get开始时间()).append("%' ");
			if(!StringUtil.isEmpty(cdt_.get规格型号()))
				sql.append(" and a.规格型号 like '%").append(cdt_.get开始时间()).append("%' ");
			if(!StringUtil.isEmpty(cdt_.get类型分类()))
				sql.append(" and a.类型分类 = '").append(cdt_.get类型分类()).append("' ");
		}
		sql.append(" order by a.消耗日期 desc ");
		return sql.toString();
	}
}
